from flask import Blueprint, render_template, request, redirect, url_for, flash, jsonify, send_file, g, session
from db_sqlite_manager import XHSDataDB
import json
import io
import datetime
import os
from werkzeug.utils import secure_filename
import subprocess
import sys
from pathlib import Path
import hashlib
from functools import wraps

# 创建蓝图，不指定前缀，会注册到根路径
xhs_bp = Blueprint('xhs', __name__)

# 使用Flask的g对象存储线程特定的数据库连接
def get_db():
    """获取数据库连接 - 每个请求一个新连接以确保线程安全"""
    if 'xhs_db' not in g:
        # 获取应用根目录
        app_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        db_path = os.path.join(app_root, 'xhs_database.db')
        g.xhs_db = XHSDataDB(db_path)
    return g.xhs_db

# 登录装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            flash('请先登录', 'warning')
            return redirect(url_for('xhs.login'))
        return f(*args, **kwargs)
    return decorated_function

# 管理员权限装饰器
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            flash('请先登录', 'warning')
            return redirect(url_for('xhs.login'))
        if session.get('user_role', 1) != 0:  # 0是管理员角色
            flash('您没有权限访问此页面', 'danger')
            return redirect(url_for('xhs.index'))
        return f(*args, **kwargs)
    return decorated_function

# 登录页面
@xhs_bp.route('/login', methods=['GET', 'POST'])
def login():
    """用户登录页面与处理"""
    # 如果已经登录，则重定向到首页
    if 'user_id' in session:
        return redirect(url_for('xhs.index'))
    
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        remember = request.form.get('remember')
        
        # 简单的登录验证
        db = get_db()
        
        # 检查user_data表是否存在
        db.cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='user_data'")
        if not db.cursor.fetchone():
            flash('系统未配置用户表，请联系管理员', 'danger')
            return render_template('xhs/login.html')
        
        # 查询用户
        db.cursor.execute("SELECT * FROM user_data WHERE username = ?", (username,))
        user = db.cursor.fetchone()
        
        if user:
            user_dict = dict(user)
            # 检查密码 (这里假设密码是明文存储的，实际应用中应使用哈希)
            if password == user_dict['password']:
                # 检查用户状态
                if user_dict['status'] == 0:
                    flash('账号已被禁用，请联系管理员', 'danger')
                    return render_template('xhs/login.html')
                
                # 登录成功，保存用户信息到会话
                session['user_id'] = user_dict['id']
                session['username'] = user_dict['username']
                session['user_role'] = user_dict['role']
                session['real_name'] = user_dict['real_name']
                
                # 更新最后登录时间
                db.cursor.execute(
                    "UPDATE user_data SET last_login_time = ? WHERE id = ?",
                    (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), user_dict['id'])
                )
                db.conn.commit()
                
                # 设置session永久保存
                if remember:
                    session.permanent = True
                
                flash('登录成功', 'success')
                return redirect(url_for('xhs.index'))
            else:
                flash('密码错误', 'danger')
        else:
            flash('用户不存在', 'danger')
    
    return render_template('xhs/login.html')

# 注销
@xhs_bp.route('/logout')
def logout():
    """用户注销"""
    session.clear()
    flash('您已成功注销', 'success')
    return redirect(url_for('xhs.login'))

# 主页面
@xhs_bp.route('/')
@login_required
def index():
    """首页，显示统计信息和快速导航"""
    db = get_db()
    stats = db.get_dashboard_statistics()
    
    # 获取最近任务
    recent_tasks = stats.get('recent_tasks', [])
    
    # 获取热门笔记
    popular_notes = stats.get('popular_notes', [])
    
    # 获取笔记类型统计(只获取前5条)
    note_types = db.get_note_types_statistics(limit=5)
    
    return render_template('xhs/index.html', 
                          stats=stats,
                          recent_tasks=recent_tasks,
                          popular_notes=popular_notes,
                          note_types=note_types)

# 数据大屏
@xhs_bp.route('/data-screen')
def data_screen():
    """数据大屏页面 - 使用通用大屏目录模板"""
    db = get_db()
    
    try:
        print("\n=== 加载小红书数据大屏数据 ===")
        
        # 获取统计数据
        stats = db.get_dashboard_statistics()
        
        # 获取关键词统计
        keyword_stats = db.get_keyword_statistics()
        
        # 获取位置统计
        location_stats = db.get_location_statistics()
        
        # 获取最新笔记列表
        recent_notes = db.get_all_notes(limit=10)
        
        # 获取热门用户
        top_users = db.get_all_users(limit=10)
        
        # 从edit_time提取的位置统计
        locations, _ = db.get_locations_from_edit_time(limit=10)
        
        # 准备传递给前端的数据
        data = {
            'stats': {
                'total_notes': stats.get('total_notes', 0),
                'total_comments': stats.get('total_comments', 0),
                'total_users': stats.get('total_users', 0),
                'total_tasks': stats.get('total_tasks', 0)
            },
            'keyword_stats': keyword_stats,
            'location_stats': locations,
            'recent_notes': recent_notes,
            'top_users': top_users
        }
        
        print("数据准备完成，渲染页面")
        print("=== 小红书数据大屏加载完成 ===\n")
        
        # 使用通用大屏模板
        return render_template('data_screen.html', 
                             data=data,
                             data_json=json.dumps(data, default=str))
    
    except Exception as e:
        import traceback
        print(f"数据大屏加载错误: {e}")
        print(traceback.format_exc())
        return render_template('errors/500.html', error=str(e)), 500

######################################
# 采集任务管理
######################################

@xhs_bp.route('/tasks')
def tasks_list():
    """采集任务列表页面"""
    db = get_db()
    page = int(request.args.get('page', 1))
    limit = 20
    offset = (page - 1) * limit
    
    try:
        tasks = db.get_all_tasks(limit=limit, offset=offset)
        
        # 获取任务总数（用于分页）
        db.cursor.execute("SELECT COUNT(*) as count FROM collection_tasks")
        total = db.cursor.fetchone()['count']
    except Exception as e:
        # 表可能不存在或其他错误
        print(f"采集任务查询错误: {str(e)}")
        tasks = []
        total = 0
    
    return render_template('xhs/tasks.html',
                          tasks=tasks,
                          current_page=page,
                          total_pages=(total // limit) + (1 if total % limit > 0 else 0))

@xhs_bp.route('/tasks/add', methods=['GET', 'POST'])
def add_task():
    """添加采集任务页面"""
    if request.method == 'POST':
        keyword = request.form.get('keyword')
        target_notes = request.form.get('target_notes_count', type=int)
        target_comments = request.form.get('target_comments_count', type=int)
        
        if not keyword:
            flash('请输入关键词', 'danger')
            return redirect(url_for('xhs.add_task'))
        
        db = get_db()
        task_data = {
            'keyword': keyword,
            'start_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            'target_notes_count': target_notes or 100,
            'target_comments_count': target_comments or 1000,
            'actual_notes_count': 0,
            'actual_comments_count': 0,
            'status': '等待中',
        }
        
        task_id = db.add_task(task_data)
        
        # 启动小红书爬虫程序
        try:
            # 获取项目根目录
            base_dir = Path(__file__).parent.parent.parent
            # 构建爬虫路径
            crawler_path = base_dir / "xhs_collection-master" / "xhs_crawler_gradio.py"
            # 获取爬虫目录
            crawler_dir = base_dir / "xhs_collection-master"
            
            # 先检查爬虫文件是否存在
            if not crawler_path.exists():
                raise FileNotFoundError(f"找不到爬虫文件: {crawler_path}")
            
            print(f"准备启动爬虫程序: {crawler_path}")
            print(f"爬虫工作目录: {crawler_dir}")
            print(f"任务参数: 关键词={keyword}, 笔记数={target_notes}, 评论数={target_comments}, 任务ID={task_id}")
            
            # 启动爬虫进程并传入参数
            command = [sys.executable, str(crawler_path), "--keyword", keyword, 
                      "--notes", str(target_notes), "--comments", str(target_comments),
                      "--task_id", str(task_id), "--no-ui"]
            
            # 创建日志目录
            log_dir = base_dir / "logs"
            if not log_dir.exists():
                log_dir.mkdir(parents=True)
            
            # 创建日志文件
            log_file = log_dir / f"task_{task_id}_{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
            
            # 检查数据库表结构，确认是否有remarks字段
            db.cursor.execute("PRAGMA table_info(collection_tasks)")
            columns = [column[1] for column in db.cursor.fetchall()]
            has_remarks = 'remarks' in columns
            
            # 更新任务状态信息
            update_data = {
                'status': '处理中',
            }
            
            # 使用subprocess启动独立进程，不阻塞主应用
            with open(log_file, 'w', encoding='utf-8') as f:
                process = subprocess.Popen(command, 
                                stdout=f,  # 标准输出重定向到日志文件
                                stderr=f,  # 标准错误重定向到日志文件
                                shell=False,  # 不使用shell执行
                                cwd=crawler_dir)  # 设置爬虫目录为工作目录
            
            process_info = f"爬虫进程已启动，PID: {process.pid}，日志文件: {log_file.name}"
            print(process_info)
            
            # 如果表中有remarks字段，则更新remarks
            if has_remarks:
                update_data['remarks'] = process_info
            
            # 更新任务状态
            db.update_task(task_id, update_data)
            
            flash('任务创建成功，爬虫已启动，请在任务详情中查看进度', 'success')
        except Exception as e:
            flash(f'爬虫启动失败: {str(e)}', 'danger')
            # 更新任务状态为失败
            db.update_task(task_id, {'status': '启动失败'})
        
        return redirect(url_for('xhs.task_detail', task_id=task_id))
    
    return render_template('xhs/task_form.html')

@xhs_bp.route('/tasks/<int:task_id>')
def task_detail(task_id):
    """任务详情页面"""
    db = get_db()
    task = db.get_task_by_id(task_id)
    
    if not task:
        flash('任务不存在', 'danger')
        return redirect(url_for('xhs.tasks_list'))
    
    # 获取该任务关键词相关的笔记
    notes = db.get_notes_by_keyword(task['keyword'], limit=20)
    
    return render_template('xhs/task_detail.html',
                          task=task,
                          notes=notes)

@xhs_bp.route('/tasks/<int:task_id>/delete', methods=['POST'])
def delete_task(task_id):
    """删除任务"""
    db = get_db()
    success = db.delete_task(task_id)
    
    if success:
        flash('任务已删除', 'success')
    else:
        flash('删除失败', 'danger')
    
    return redirect(url_for('xhs.tasks_list'))

######################################
# 笔记管理
######################################

@xhs_bp.route('/notes')
@xhs_bp.route('/notes/')
def notes_list():
    """笔记列表页面"""
    db = get_db()
    page = int(request.args.get('page', 1))
    keyword = request.args.get('keyword', '')
    limit = 20
    offset = (page - 1) * limit
    
    notes = db.get_all_notes(limit=limit, offset=offset, keyword=keyword)
    
    # 获取笔记总数（用于分页）
    query = "SELECT COUNT(*) as count FROM notes"
    params = []
    if keyword:
        query += " WHERE keyword LIKE ?"
        params.append(f"%{keyword}%")
    
    db.cursor.execute(query, tuple(params))
    total = db.cursor.fetchone()['count']
    
    # 获取所有关键词（用于筛选）
    db.cursor.execute("SELECT DISTINCT keyword FROM notes WHERE keyword IS NOT NULL AND keyword != ''")
    keywords = [row['keyword'] for row in db.cursor.fetchall()]
    
    return render_template('xhs/notes.html',
                          notes=notes,
                          current_page=page,
                          total_pages=(total // limit) + (1 if total % limit > 0 else 0),
                          search_keyword=keyword,
                          keywords=keywords)

@xhs_bp.route('/notes/<int:note_id>')
@xhs_bp.route('/notes/<int:note_id>/')
def note_detail(note_id):
    """笔记详情页面"""
    db = get_db()
    note = db.get_note_by_id(note_id)
    
    if not note:
        flash('笔记不存在', 'danger')
        return redirect(url_for('xhs.notes_list'))
    
    # 获取该笔记的评论
    comments = db.get_comments_by_note(note_id, limit=50)
    
    # 获取用户信息
    user = None
    if 'author' in note:
        db.cursor.execute("SELECT * FROM users WHERE user_name = ?", (note['author'],))
        user_row = db.cursor.fetchone()
        if user_row:
            user = dict(user_row)
    
    return render_template('xhs/note_detail.html',
                          note=note,
                          comments=comments,
                          user=user)

@xhs_bp.route('/notes/add', methods=['GET', 'POST'])
@xhs_bp.route('/notes/add/', methods=['GET', 'POST'])
def add_note():
    """添加笔记页面"""
    db = get_db()
    
    # 获取所有关键词（用于表单选择）
    db.cursor.execute("SELECT DISTINCT keyword FROM notes WHERE keyword IS NOT NULL AND keyword != ''")
    keywords = [row['keyword'] for row in db.cursor.fetchall()]
    
    if request.method == 'POST':
        # 收集表单数据
        title = request.form.get('title')
        author = request.form.get('author')
        content = request.form.get('content')
        keyword = request.form.get('keyword')
        edit_time = request.form.get('edit_time', datetime.datetime.now().strftime('%Y-%m-%d'))
        url = request.form.get('url', '')
        
        # 验证必填字段
        if not title:
            flash('标题不能为空', 'danger')
            return render_template('xhs/note_form.html', keywords=keywords, action='add')
        
        # 构建笔记数据
        note_data = {
            'title': title,
            'author': author,
            'content': content,
            'keyword': keyword,
            'edit_time': edit_time,
            'url': url,
            'collect_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            'likes': request.form.get('likes', 0),
            'collects': request.form.get('collects', 0),
            'comments': request.form.get('comments', 0)
        }
        
        # 添加笔记
        note_id = db.add_note(note_data)
        
        if note_id > 0:
            flash('笔记添加成功', 'success')
            return redirect(url_for('xhs.note_detail', note_id=note_id))
        else:
            flash('笔记添加失败，请检查数据', 'danger')
    
    return render_template('xhs/note_form.html', keywords=keywords, action='add')

@xhs_bp.route('/notes/<int:note_id>/edit', methods=['GET', 'POST'])
@xhs_bp.route('/notes/<int:note_id>/edit/', methods=['GET', 'POST'])
def edit_note(note_id):
    """编辑笔记页面"""
    db = get_db()
    note = db.get_note_by_id(note_id)
    
    if not note:
        flash('笔记不存在', 'danger')
        return redirect(url_for('xhs.notes_list'))
    
    # 获取所有关键词（用于表单选择）
    db.cursor.execute("SELECT DISTINCT keyword FROM notes WHERE keyword IS NOT NULL AND keyword != ''")
    keywords = [row['keyword'] for row in db.cursor.fetchall()]
    
    if request.method == 'POST':
        # 收集表单数据
        title = request.form.get('title')
        author = request.form.get('author')
        content = request.form.get('content')
        keyword = request.form.get('keyword')
        edit_time = request.form.get('edit_time')
        
        # 验证必填字段
        if not title:
            flash('标题不能为空', 'danger')
            return render_template('xhs/note_form.html', note=note, keywords=keywords, action='edit')
        
        # 构建更新数据
        update_data = {
            'title': title,
            'author': author,
            'content': content,
            'keyword': keyword,
            'edit_time': edit_time,
            'likes': request.form.get('likes', note.get('likes', 0)),
            'collects': request.form.get('collects', note.get('collects', 0)),
            'comments': request.form.get('comments', note.get('comments', 0))
        }
        
        # 更新笔记
        success = db.update_note(note_id, update_data)
        
        if success:
            flash('笔记更新成功', 'success')
            return redirect(url_for('xhs.note_detail', note_id=note_id))
        else:
            flash('笔记更新失败，请检查数据', 'danger')
    
    return render_template('xhs/note_form.html', note=note, keywords=keywords, action='edit')

@xhs_bp.route('/notes/<int:note_id>/delete', methods=['POST'])
@xhs_bp.route('/notes/<int:note_id>/delete/', methods=['POST'])
def delete_note(note_id):
    """删除笔记"""
    db = get_db()
    
    # 先检查笔记是否存在
    note = db.get_note_by_id(note_id)
    if not note:
        flash('笔记不存在', 'danger')
        return redirect(url_for('xhs.notes_list'))
    
    # 删除笔记
    success = db.delete_note(note_id)
    
    if success:
        flash('笔记已成功删除', 'success')
    else:
        flash('删除笔记时发生错误', 'danger')
    
    return redirect(url_for('xhs.notes_list'))

######################################
# 用户管理
######################################

@xhs_bp.route('/users')
def users_list():
    """用户列表页面"""
    db = get_db()
    page = int(request.args.get('page', 1))
    search = request.args.get('search', '')
    limit = 20
    offset = (page - 1) * limit
    
    try:
        query = "SELECT * FROM users"
        params = []
        
        if search:
            query += " WHERE user_name LIKE ? OR location LIKE ?"
            params.extend([f"%{search}%", f"%{search}%"])
        
        query += " ORDER BY followers_count DESC LIMIT ? OFFSET ?"
        params.extend([limit, offset])
        
        db.cursor.execute(query, tuple(params))
        users = [dict(row) for row in db.cursor.fetchall()]
        
        # 获取用户总数（用于分页）
        count_query = "SELECT COUNT(*) as count FROM users"
        count_params = []
        
        if search:
            count_query += " WHERE user_name LIKE ? OR location LIKE ?"
            count_params.extend([f"%{search}%", f"%{search}%"])
        
        db.cursor.execute(count_query, tuple(count_params))
        total = db.cursor.fetchone()['count']
    except Exception as e:
        # 表可能不存在或其他错误
        print(f"用户列表查询错误: {str(e)}")
        users = []
        total = 0
    
    return render_template('xhs/users.html',
                          users=users,
                          current_page=page,
                          total_pages=(total // limit) + (1 if total % limit > 0 else 0),
                          search=search)

@xhs_bp.route('/users/<user_id>')
def user_detail(user_id):
    """用户详情页面"""
    db = get_db()
    user = db.get_user_by_id(user_id)
    
    if not user:
        flash('用户不存在', 'danger')
        return redirect(url_for('xhs.users_list'))
    
    # 获取用户的笔记
    db.cursor.execute(
        "SELECT * FROM notes WHERE author = ? ORDER BY edit_time DESC LIMIT 10", 
        (user['user_name'],)
    )
    notes = [dict(row) for row in db.cursor.fetchall()]
    
    # 获取用户的评论
    db.cursor.execute(
        "SELECT * FROM comments WHERE user_id = ? ORDER BY comment_time DESC LIMIT 20", 
        (user_id,)
    )
    comments = [dict(row) for row in db.cursor.fetchall()]
    
    return render_template('xhs/user_detail.html',
                          user=user,
                          notes=notes,
                          comments=comments)

######################################
# 位置分析
######################################

@xhs_bp.route('/locations')
def locations_list():
    """位置分析页面 - 从notes表的edit_time字段中提取位置信息进行分析"""
    db = get_db()
    page = int(request.args.get('page', 1))
    search = request.args.get('search', '')
    limit = 20
    offset = (page - 1) * limit
    
    try:
        # 使用新方法从edit_time字段提取位置信息
        locations, total = db.get_locations_from_edit_time(
            limit=limit, 
            offset=offset,
            search=search
        )
        
    except Exception as e:
        # 处理可能的错误
        print(f"位置列表查询错误: {str(e)}")
        import traceback
        print(traceback.format_exc())
        locations = []
        total = 0
    
    return render_template('xhs/locations.html',
                          locations=locations,
                          current_page=page,
                          total_pages=(total // limit) + (1 if total % limit > 0 else 0),
                          search=search)

@xhs_bp.route('/locations/<string:location_name>')
def location_detail(location_name):
    """位置详情页面 - 显示从edit_time字段中提取的特定位置的详情"""
    db = get_db()
    
    try:
        # 获取该位置的笔记
        db.cursor.execute(
            """
            SELECT * FROM notes 
            WHERE edit_time LIKE ? 
            ORDER BY likes DESC, collects DESC
            LIMIT 20
            """, 
            (f"%{location_name}%",)
        )
        notes = [dict(row) for row in db.cursor.fetchall()]
        
        # 获取该位置的笔记数量
        db.cursor.execute(
            "SELECT COUNT(*) as count FROM notes WHERE edit_time LIKE ?",
            (f"%{location_name}%",)
        )
        note_count = db.cursor.fetchone()['count']
        
        # 创建位置信息对象
        location = {
            'location_name': location_name,
            'count': note_count,
            'description': f'从笔记edit_time字段中提取的"{location_name}"位置信息'
        }
        
        return render_template('xhs/location_detail.html',
                            location=location,
                            notes=notes)
    
    except Exception as e:
        flash(f'获取位置详情失败: {str(e)}', 'danger')
        return redirect(url_for('xhs.locations_list'))

######################################
# 数据导入导出
######################################

@xhs_bp.route('/export', methods=['GET', 'POST'])
def export_data():
    """数据导出页面"""
    db = get_db()
    
    if request.method == 'POST':
        # 获取当前时间戳
        timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"xiaohongshu_data_{timestamp}.json"
        file_path = os.path.join(os.getcwd(), filename)
        
        # 导出数据
        success = db.export_to_json(file_path)
        
        if success:
            # 发送文件给客户端
            return send_file(
                file_path,
                mimetype='application/json',
                as_attachment=True,
                download_name=filename
            )
        else:
            flash('导出失败，请查看日志', 'danger')
            return redirect(url_for('xhs.export_data'))
    
    # 获取数据库表的记录数
    stats = {}
    for table in ['collection_tasks', 'notes', 'comments', 'users', 'locations']:
        db.cursor.execute(f"SELECT COUNT(*) as count FROM {table}")
        stats[table] = db.cursor.fetchone()['count']
    
    return render_template('xhs/export.html', stats=stats)

@xhs_bp.route('/import', methods=['GET', 'POST'])
def import_data():
    """数据导入页面"""
    if request.method == 'POST':
        # 检查是否有文件
        if 'file' not in request.files:
            flash('没有选择文件', 'danger')
            return redirect(request.url)
        
        file = request.files['file']
        
        # 检查文件名
        if file.filename == '':
            flash('没有选择文件', 'danger')
            return redirect(request.url)
        
        # 检查文件扩展名
        if not file.filename.endswith('.json'):
            flash('只支持JSON格式的文件', 'danger')
            return redirect(request.url)
        
        # 保存文件到临时位置
        file_path = os.path.join(os.getcwd(), 'temp_import.json')
        file.save(file_path)
        
        # 导入数据
        db = get_db()
        success = db.import_from_json(file_path)
        
        # 删除临时文件
        if os.path.exists(file_path):
            os.remove(file_path)
        
        if success:
            flash('数据导入成功', 'success')
            return redirect(url_for('xhs.index'))
        else:
            flash('导入失败，请查看日志', 'danger')
            return redirect(request.url)
    
    return render_template('xhs/import.html')

######################################
# 数据统计与分析
######################################

@xhs_bp.route('/statistics')
def statistics():
    """数据统计与分析页面"""
    db = get_db()
    
    # 获取基本统计数据
    stats = db.get_dashboard_statistics()
    
    # 添加任务状态统计
    try:
        db.cursor.execute(
            """
            SELECT status, COUNT(*) as count 
            FROM collection_tasks 
            GROUP BY status
            """
        )
        task_status = {}
        for row in db.cursor.fetchall():
            status = row['status']
            if '完成' in status or '成功' in status:
                task_status['success'] = row['count']
            elif '处理中' in status or '运行' in status:
                task_status['running'] = row['count']
            elif '失败' in status or '错误' in status:
                task_status['failed'] = row['count']
            else:
                task_status['pending'] = task_status.get('pending', 0) + row['count']
        stats['task_status'] = task_status
    except Exception as e:
        print(f"获取任务状态统计失败: {e}")
        stats['task_status'] = {'success': 0, 'running': 0, 'failed': 0, 'pending': 0}
    
    # 点赞数量分布
    try:
        ranges = ['0-100', '101-500', '501-1000', '1001-5000', '5000+']
        db.cursor.execute(
            """
            SELECT 
                CASE 
                    WHEN likes BETWEEN 0 AND 100 THEN '0-100'
                    WHEN likes BETWEEN 101 AND 500 THEN '101-500'
                    WHEN likes BETWEEN 501 AND 1000 THEN '501-1000'
                    WHEN likes BETWEEN 1001 AND 5000 THEN '1001-5000'
                    ELSE '5000+'
                END as range,
                COUNT(*) as count
            FROM notes
            GROUP BY range
            ORDER BY 
                CASE range
                    WHEN '0-100' THEN 1
                    WHEN '101-500' THEN 2
                    WHEN '501-1000' THEN 3
                    WHEN '1001-5000' THEN 4
                    ELSE 5
                END
            """
        )
        likes_distribution = {'ranges': ranges, 'counts': [0] * len(ranges)}
        
        for row in db.cursor.fetchall():
            range_idx = ranges.index(row['range'])
            likes_distribution['counts'][range_idx] = row['count']
        
        stats['likes_distribution'] = likes_distribution
    except Exception as e:
        print(f"获取点赞分布统计失败: {e}")
        stats['likes_distribution'] = {
            'ranges': ranges,
            'counts': [0] * len(ranges)
        }
    
    # 获取关键词统计
    keyword_stats = db.get_keyword_statistics()
    
    # 将关键词统计转换为词云数据格式
    stats['keywords_cloud'] = [{'name': item['keyword'], 'value': item['count']} for item in keyword_stats]
    
    # 获取位置统计
    location_stats = db.get_location_statistics()
    stats['location_distribution'] = [{'name': item['location_name'], 'value': item['count']} for item in location_stats]
    
    # 获取每日数据增长情况
    db.cursor.execute(
        """
        SELECT DATE(collect_time) as date, COUNT(*) as count 
        FROM notes 
        GROUP BY DATE(collect_time) 
        ORDER BY date
        """
    )
    daily_notes = [dict(row) for row in db.cursor.fetchall()]
    
    db.cursor.execute(
        """
        SELECT DATE(collect_time) as date, COUNT(*) as count 
        FROM comments 
        GROUP BY DATE(collect_time) 
        ORDER BY date
        """
    )
    daily_comments = [dict(row) for row in db.cursor.fetchall()]
    
    # 采集趋势数据
    if daily_notes:
        dates = [row['date'] for row in daily_notes]
        notes_counts = [row['count'] for row in daily_notes]
        comments_counts = []
        
        # 确保每个日期都有对应的评论数据
        for date in dates:
            found = False
            for comment in daily_comments:
                if comment['date'] == date:
                    comments_counts.append(comment['count'])
                    found = True
                    break
            if not found:
                comments_counts.append(0)
        
        stats['collection_trend'] = {
            'dates': dates,
            'notes': notes_counts,
            'comments': comments_counts
        }
    else:
        # 如果没有数据，提供默认值
        stats['collection_trend'] = {
            'dates': ['1月', '2月', '3月', '4月', '5月', '6月', '7月'],
            'notes': [120, 132, 101, 134, 90, 180, 210],
            'comments': [220, 182, 191, 234, 290, 330, 310]
        }
    
    # 互动分析数据
    stats['interactions'] = {
        'categories': ['美食', '旅行', '穿搭', '美妆', '数码'],
        'likes': [120, 200, 150, 80, 70],
        'collects': [80, 150, 100, 50, 40],
        'comments': [40, 80, 50, 30, 20]
    }
    
    # 用户地区分布
    db.cursor.execute(
        """
        SELECT location, COUNT(*) as count 
        FROM users 
        WHERE location IS NOT NULL AND location != ''
        GROUP BY location 
        ORDER BY count DESC 
        LIMIT 10
        """
    )
    user_locations = [dict(row) for row in db.cursor.fetchall()]
    
    # 添加额外的数据字段
    stats['tasks_count'] = stats.get('total_tasks', 0)
    stats['notes_count'] = stats.get('total_notes', 0)
    stats['comments_count'] = stats.get('total_comments', 0)
    stats['users_count'] = stats.get('total_users', 0)
    
    return render_template('xhs/statistics.html',
                          stats=stats,
                          keyword_stats=keyword_stats,
                          location_stats=location_stats,
                          daily_notes=daily_notes,
                          daily_comments=daily_comments,
                          user_locations=user_locations)

@xhs_bp.route('/note-types')
def note_types():
    """笔记类型统计页面"""
    db = get_db()
    
    # 获取所有笔记类型统计
    types = db.get_note_types_statistics(limit=100)  # 获取更多类型，最多100个
    
    # 获取各类型的示例笔记(每种类型最多3条)
    types_with_examples = []
    for type_item in types:
        keyword = type_item['keyword']
        example_notes = db.get_notes_by_keyword(keyword, limit=3)
        types_with_examples.append({
            'keyword': keyword,
            'count': type_item['count'],
            'examples': example_notes
        })
    
    return render_template('xhs/note_types.html',
                          types=types_with_examples,
                          title="笔记类型统计")

# 在Blueprint请求结束时关闭数据库连接
@xhs_bp.teardown_app_request
def close_db(exception):
    """请求结束时关闭数据库连接"""
    db = g.pop('xhs_db', None)
    if db is not None:
        db.close() 